package com.futvan.z.system.zetlin;

import com.futvan.z.framework.common.bean.Code;
import com.futvan.z.framework.common.bean.Result;
import com.futvan.z.framework.core.SuperService;
import com.futvan.z.framework.core.z;
import com.futvan.z.framework.util.DBUtil;
import com.futvan.z.system.zdb.z_db;
import com.futvan.z.system.zdb.z_db_table;
import com.futvan.z.system.zdb.z_db_table_column;
import com.futvan.z.system.zform.z_form_table;
import com.futvan.z.system.zform.z_form_table_column;
import com.futvan.z.system.zjob.z_job;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import java.util.HashMap;
import java.util.List;
@Service
public class ZetlinService extends SuperService{

	/**
	 * 保存
	 * @param bean
	 * @param request
	 * @return
	 * @throws Exception 
	 */
	public Result Save(HashMap<String,String> bean,HttpServletRequest request) throws Exception {
		Result result = new Result();
		String PageType = bean.get("PageType");
		if(z.isNotNull(PageType)) {
			if("add".equals(PageType)) {
				CreateDetail(bean);
				result = insert(bean,request);
			}else if("edit".equals(PageType)) {
				CreateDetail(bean);
				result = update(bean,request);
			}else {
				result.setCode(Code.ERROR);
				result.setMsg("页面保存类型错误|PageType:"+PageType);
			}
		}else {
			result.setCode(Code.ERROR);
			result.setMsg("页面保存类型为空|PageType is null");
		}
		return result;
	}

	/**
	 * 生成字段映射表数据
	 * @param bean
	 * @return
	 * @throws Exception 
	 */
	public Result CreateDetail(HashMap<String,String> bean) throws Exception {
		Result result = new Result();
		String zid = bean.get("zid");
		if(z.isNotNull(zid)) {
			//获取SQL
			String sqlinfo = bean.get("sqlinfo");
			if(z.isNotNull(sqlinfo)) {
				//获取目标数据库连接
				String form_db = bean.get("form_db");
				if(z.isNotNull(form_db)) {
					SqlSessionTemplate s = z.dbs.get(form_db);
					if(z.isNotNull(s)) {
						//删除源字段映射关系
						delete("z_etl_in_detail_delete_pid", zid);

						//解析SQL获取字段
						z_db_table table = DBUtil.parseSQL(sqlinfo, s);
						//遍历所有字段，生成映射关系表数据
						for (z_db_table_column column : table.getZ_db_table_column_list()) {
							z_etl_in_detail d = new z_etl_in_detail();
							d.setZid(z.newZid("z_etl_in_detail"));
							d.setPid(zid);
							d.setForm_columnid(column.getColumn_id());
							//判断目录数据库表中，是否有SQL对应字段，如果有，自动关联
							Result vr = verificationIsToColumnid(bean.get("to_db"),bean.get("to_db_table"),column.getColumn_id());
							if(Code.SUCCESS.equals(vr.getCode())) {
								d.setTo_columnid(String.valueOf(vr.getData()));
							}
							int num = insert("z_etl_in_detail_insert", d);
							if(num!=1) {
								z.Exception("z_etl_in_detail_insert 返回结果集不为0");
							}
						}
						result.setCode(Code.SUCCESS);
					}else {
						result.setCode(Code.ERROR);
						result.setMsg("CreateDetail SqlSessionTemplate is null");
					}
				}else {
					result.setCode(Code.ERROR);
					result.setMsg("CreateDetail form_db is null");
				}
			}else {
				result.setCode(Code.ERROR);
				result.setMsg("CreateDetail sqlinfo is null");
			}
		}else {
			result.setCode(Code.ERROR);
			result.setMsg("CreateDetail zid is null");
		}
		return result;
	}

	/**
	 * 判断目录数据库表中，是否有SQL对应字段，如果有，自动关联
	 * @param columnid 
	 * @return
	 */
	private Result verificationIsToColumnid(String to_db,String tableid, String columnid) {
		Result result = new Result();
		//转小写
		columnid = columnid.toUpperCase();
		result.setCode(Code.ERROR);
		//在目标数据库中查找，是否有对应名字段，如果找到自动关联
		if(z.isNull(to_db)) {
			//核心库中查找
			z_form_table t = z.tables.get(tableid);
			if(z.isNotNull(t)) {
				List<z_form_table_column>  columnList = t.getZ_form_table_column_list();
				for (z_form_table_column ztcolumnid : columnList) {
					if(columnid.equals(ztcolumnid.getColumn_id().toUpperCase())) {
						result.setCode(Code.SUCCESS);
						result.setData(ztcolumnid.getColumn_id());
						break;
					}
				}
			}
		}else {
			//第三方库中查询
			z_db db = z.dbsMap.get(to_db);
			if(z.isNotNull(db)) {
				List<z_db_table> tableList = db.getZ_db_table_list();
				for (z_db_table table : tableList) {
					if(tableid.equals(table.getTable_id())) {
						List<z_db_table_column> columnList = table.getZ_db_table_column_list();
						for (z_db_table_column column : columnList) {
							if(columnid.equals(column.getColumn_id().toUpperCase())) {
								result.setCode(Code.SUCCESS);
								result.setData(column.getColumn_id());
								break;
							}
						}
					}
				}
			}
		}
		return result;
	}
	
	
	/**
	 * 根据表名获取表ZID
	 * @return
	 * @throws Exception
	 */
	public Result getZDBTableZidForTelZid(String zid) {
		Result result = new Result();
		if(z.isNotNull(zid)) {
			z_etl_in etl = selectBean("z_etl_in_select_zid", zid);
			if(z.isNotNull(etl)) {
				String toTable = etl.getTo_db_table();
				if(z.isNotNull(toTable)) {
					z_db_table table = z.dbTableMap.get(etl.getTo_db()+"_"+toTable);
					if(z.isNotNull(table)) {
						result.setCode(Code.SUCCESS);
						result.setData(table.getZid());
					}else {
						result.setCode(Code.ERROR);
						result.setMsg("未获取到表信息");
					}
				}else {
					result.setCode(Code.ERROR);
					result.setMsg("toTable is null");
				}
			}else {
				result.setCode(Code.ERROR);
				result.setMsg("根据zid未找到对应etl数据");
			}
		}else {
			result.setCode(Code.ERROR);
			result.setMsg("zid is null");
		}
		return result;
	}

	/**
	 * 绑定定时执行任务
	 * @param bean
	 * @param request
	 * @return
	 */
	public Result z_etl_in_edit_addJob(HashMap<String, String> bean, HttpServletRequest request) {
		Result result = new Result();
		String zid = bean.get("zid");
		if(z.isNotNull(zid)) {
			z_etl_in e = z.etls.get(zid);
			if(z.isNull(e)) {
				e = sqlSession.selectOne("z_etl_in_select_zid", zid);
				List<z_etl_in_detail> z_etl_in_detail_list = sqlSession.selectList("z_etl_in_detail_select_pid", zid);
				e.setZ_etl_in_detail_list(z_etl_in_detail_list);
				z.etls.put(zid, e);
			}
			if(z.isNotNull(e)) {
				z_job job = new z_job();
				String jobzid = z.newZid("z_job");
				job.setZid(jobzid);
				job.setIsstart("0");
				job.setJobname(e.getName());
				job.setJobtype("1");//数据抽取任务类型
				job.setEtlid(e.getZid());
				int num = insert("z_job_insert", job);
				if(num==1) {
					result.setCode(Code.SUCCESS);
					result.setMsg("绑定定时执行任务成功，请您在“任务管理”功能中维护数据抽取时间");
					result.setData(jobzid);
				}else {
					result.setCode(Code.ERROR);
					result.setMsg("绑定定时执行任务出错：执行保存数据时，返回结果为|"+num);
				}
			}else {
				result.setCode(Code.ERROR);
				result.setMsg("未知错，ZID无效");
			}
		}else {
			result.setCode(Code.ERROR);
			result.setMsg("zid is null");
		}
		return result;
	}
}
